package dao;

import com.db.DBConn;

import model.Student;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

public class StudentDao {
    public Collection getDatas() throws SQLException {
        DBConn dbConn = new DBConn();
        ResultSet resultSet = dbConn.queryData("select * from student order by id");
        Collection col = new Vector();
        while (resultSet.next()) {
            Student student = new Student();
            student.setId(resultSet.getString("id"));
            student.setSno(resultSet.getString("sno"));
            student.setSname(resultSet.getString("sname"));
            student.setPassword(resultSet.getString("password"));
            student.setEmail(resultSet.getString("email"));
            col.add(student);
        }
        return col;
    }

    //提取教师信息by id
    public Student getData(String id) throws SQLException {
        DBConn db = new DBConn();
        ResultSet rs = db.queryData("select * from student where id="+id);
        if(rs.next()) {
            Student student = new Student();
            student.setId(id);
            student.setSno(rs.getString("sno"));
            student.setSname(rs.getString("sname"));
            student.setPassword(rs.getString("password"));
            student.setEmail(rs.getString("email"));
            return student;
        }else {
            return null;
        }
    }

    //修改数据
    public void updateData(Student student) throws SQLException {
        DBConn db = new DBConn();
        //String ano = student.getAno();
        db.updateData("update student set sno='"+student.getSno()+"',sname='"+student.getSname()+"',password='"+student.getPassword()+"',email='"+student.getEmail()+"' where id="+student.getId());
    }

    //新增数据
    public void insertData(Student student) throws SQLException {
        DBConn db = new DBConn();
        db.updateData("insert into student(sno,sname,password,email) values('"+student.getSno()+"','"+student.getSname()+"','"+student.getPassword()+"','"+student.getEmail()+"')");
    }

    //删除数据
    public void deleData(String id) throws SQLException {
        DBConn db = new DBConn();
        db.updateData("delete from student where id="+id);

    }

    //通过学号查询姓名
    public String getNameByNo(String sno) {
        DBConn db = new DBConn();
        try {
            ResultSet rs = db.queryData("select sname from student where sno='"+sno+"'");
            if (rs.next()) {
                String sname = rs.getString("sname");
                return sname;
            }else {
                return null;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }
    }
    public boolean getDataBySno(String sno,String sname){
        DBConn db = new DBConn();
        try{
            ResultSet rs = db.queryData("select * from student where sno='"+sno+"'and sname = '"+sname+"'");
            if(rs.next())
                return true;
            else
                return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
